clear 
cd ..\..

local fname build_1_import_raw_data
capture log close
local c_date = c(current_date)
local c_time = c(current_time)
local c_time_date = "`c_date'"+"_"+"`c_time'"
local c_time_date = subinstr("`c_time_date '", ":", "_", .)
local c_time_date = subinstr("`c_time_date '", " ", "_", .)
log using "output\logs\`fname'_`c_time_date'.txt", replace text

// CREATE DIRECTORY FOR STORING TEMP FILES BEFORE MERGING IN 2_clean_data_elec.do
cap mkdir "intermediate_data\data_cleaned_for_merge"

// IMPORT RAW DATA

********************************************************************************
*                     ELECTRIC Consumption Data                                *
********************************************************************************

import delimited using .\consumption\2014_ele.csv,clear
save .\input\Consumption_2014_elec.dta, replace

import delimited using .\consumption\2015_ele.csv,clear
save .\input\Consumption_2015_elec.dta, replace

use .\input\Consumption_2014_elec.dta,clear
foreach num of numlist 2015/2018{
	append using .\input\Consumption_`num'_elec.dta
}

// drop net-metering variable and all commercial accounts 
drop tot_kwh_net_meter
drop if elecratetype=="2 - Commercial Electric"

save .\input\consumption_elec_2014-2018.dta, replace

********************************************************************************
*                              Opower  Data                                    *
********************************************************************************
import excel using .\opower_cust.xlsx, clear firstrow
save .\input\opower.dta, replace // save raw data before cleaning

// change variable name from elec_acct_id to ky_ba to match consumption dataset
rename elec_acct_id ky_ba

// generate treatment/control group
gen group="."
replace group="treatment" if strpos(her_wave,"treatment")>0
replace group="control" if strpos(her_wave,"control")>0 & group=="."
drop if group=="."

// label accounts with no gas account number 
replace gas_acct_id="." if gas_acct_id=="N/A"
destring gas_acct_id, replace

// generate months and year
gen month=substr(her_wave,9,2)
destring(month), replace
gen year=substr(her_wave,5,4) 
destring(year), replace

// convert calendar month into unique month
replace month=-3 if month==3 & year==2013
replace month=-4 if month==4 & year==2013
replace month=month+12 if year==2015
replace month=month+24 if year==2016
replace month=month+36 if year==2017
replace month=month+48 if year==2018

// generate electric or gas report indicator
gen fuel_indicator=substr(her_wave,12,1) 
tostring fuel_indicator, replace
gen elec=0
replace elec=1 if fuel_indicator=="E" | fuel_indicator=="D"
gen gas=0
replace gas=1 if fuel_indicator=="G"|fuel_indicator=="D"

// generate opower dataset for merge with electric billing records
preserve
keep ky_ba year month group elec gas 
drop if ky_ba==. & elec==0
drop if ky_ba==. & elec!=0
drop if elec==0
rename month opower_month
save .\intermediate_data\data_cleaned_for_merge\opower_for_merge_elec.dta, replace
restore

// generate gas dataset with electric assignments for wave 3,6,7
preserve 
keep gas_acct_id year month group elec gas 
drop if gas_acct_id==. & gas==0
drop if gas_acct_id==. & gas!=0
rename gas_acct_id ky_ba 
rename month opower_month
save .\intermediate_data\data_cleaned_for_merge\opower_for_merge_gas_elec_report.dta, replace
restore

// generate electric dataset with gas assignments 
preserve 
keep ky_ba year month group elec gas 
drop if ky_ba==. & elec==0
drop if ky_ba==. & elec!=0
rename month opower_month
save .\intermediate_data\data_cleaned_for_merge\opower_for_merge_elec_with_gas_report.dta, replace
restore

********************************************************************************
*                         Demographics Data                                    *
********************************************************************************
local fuel "ele gas"
foreach var in `fuel'{
	import delimited .\res_accts_`var'_demog.csv, clear 

	rename d270_household_income_desc income_temp 
	rename d137_a1_marital_status_code married
	rename d263_household_member_count member
	rename num_yr_built_actl_trw vintage
	rename qty_area_bldg_gross_trw building_size
	rename qty_area_living_trw living_size
	rename qty_area_univ_bldg_trw uni_size
	rename situzip5 ad_serv_zip

	// destring variables
	destring member, replace ignore("NA")
	destring vintage, replace ignore("NA")
	destring uni_size, replace ignore("NA")
	destring living_size, replace ignore("NA")
	destring building_size, replace ignore("NA")

	// destring income
	gen income=. if income_temp==""
	replace income=5000 if income_temp=="$0 to $5,000" 
	replace income=10000 if income_temp=="$5,001 to $10,000" 
	replace income=15000 if income_temp=="$10,001 to $15,000"
	replace income=20000 if income_temp=="$15,001 to $20,000" 
	replace income=25000 if income_temp=="$20,001 to $25,000"
	replace income=30000 if income_temp=="$25,001 to $30,000" 
	replace income=35000 if income_temp=="$30,001 to $35,000" 
	replace income=40000 if income_temp=="$35,001 to $40,000"
	replace income=45000 if income_temp=="$40,001 to $45,000" 
	replace income=50000 if income_temp=="$45,001 to $50,000"
	replace income=55000 if income_temp=="$50,001 to $55,000" 
	replace income=60000 if income_temp=="$55,001 to $60,000" 
	replace income=65000 if income_temp=="$60,001 to $65,000"
	replace income=70000 if income_temp=="$65,001 to $70,000" 
	replace income=75000 if income_temp=="$70,001 to $75,000"
	replace income=80000 if income_temp=="$75,001 to $80,000" 
	replace income=85000 if income_temp=="$80,001 to $85,000" 
	replace income=90000 if income_temp=="$85,001 to $90,000"
	replace income=95000 if income_temp=="$90,001 to $95,000" 
	replace income=100000 if income_temp=="$95,001 to $100,000"
	replace income=105000 if income_temp=="$100,001 to $105,000" 
	replace income=110000 if income_temp=="$105,001 to $110,000" 
	replace income=115000 if income_temp=="$110,001 to $115,000"
	replace income=120000 if income_temp=="$115,001 to $120,000"
	replace income=125000 if income_temp=="$120,001 to $125,000" 
	replace income=130000 if income_temp=="$125,001 to $130,000"
	replace income=135000 if income_temp=="$130,001 to $135,000" 
	replace income=140000 if income_temp=="$135,001 to $140,000"
	replace income=145000 if income_temp=="$140,001 to $145,000" 
	replace income=150000 if income_temp=="$145,001 or more" 

	// generate dummy variables
	replace married="1" if married=="Married"
	replace married="0" if married=="Single"
	destring married, replace ignore("NA")

	drop v1 energytype market 

	save .\intermediate_data\data_cleaned_for_merge\demo_for_merge_`var'.dta, replace
}

********************************************************************************
capture log close
exit
